import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelBrandCategory {
    private final String SCHEMA = "flg";

    private void run() {
        List<ModifiedExportData> allModifiedData = new ArrayList<>();

        FileInputStream io = null;
        Workbook workbook = null;

        FileWriter writer = null;

        try {
            io = new FileInputStream("import/brand+category.xlsx");
            workbook = WorkbookFactory.create(io);
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows();

            for (int i = 1; i < rowCount; i++) {
                Row row = sheet.getRow(i);

                ModifiedExportData data = new ModifiedExportData();

                int id = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();
                data.setId(id);

                Integer activityId = Double.valueOf(row.getCell(1).getNumericCellValue()).intValue();
                data.setActivityId(activityId);

                String activityName = row.getCell(2).getStringCellValue();
                data.setActivityName(activityName);

                String activityCategory = row.getCell(3).getStringCellValue();
                data.setActivityCategory(activityCategory);

                Cell commissionCell = row.getCell(4);
                String commission;
                if (commissionCell.getCellTypeEnum() == CellType.STRING) {
                    commission = commissionCell.getStringCellValue().replaceAll("\n", " ");
                } else {
                    commission = String.valueOf(commissionCell.getNumericCellValue());
                }
                data.setCommission(commission);

                String settlementPeriod = row.getCell(5).getStringCellValue();
                data.setSettlementPeriod(settlementPeriod);

                Cell pcWebUrlCell = row.getCell(6);
                String pcWebUrl = "";
                if (pcWebUrlCell != null) {
                    pcWebUrl = pcWebUrlCell.getStringCellValue();
                }
                data.setPcWebUrl(pcWebUrl);

                Cell mobileWebUrlCell = row.getCell(7);
                String mobileWebUrl = "";
                if (mobileWebUrlCell != null) {
                    mobileWebUrl = mobileWebUrlCell.getStringCellValue();
                }
                data.setMobileWebUrl(mobileWebUrl);

                boolean canModifyUrl = row.getCell(8).getBooleanCellValue();
                data.setCanModifyUrl(canModifyUrl);

                Cell shortLinkCell = row.getCell(9);
                String shortLink = "";
                if (shortLinkCell != null) {
                    shortLink = shortLinkCell.getStringCellValue();
                }
                data.setShortLink(shortLink);

                Cell mobileShortLinkCell = row.getCell(10);
                String mobileShortLink = "";
                if (mobileShortLinkCell != null) {
                    mobileShortLink = mobileShortLinkCell.getStringCellValue();
                }
                data.setMobileShortLink(mobileShortLink);

                Cell encryptLinkCell = row.getCell(11);
                String encryptLink = "";
                if (encryptLinkCell != null) {
                    encryptLink = encryptLinkCell.getStringCellValue();
                }
                data.setEncryptLink(encryptLink);

                Cell mobileEncryptLinkCell = row.getCell(12);
                String mobileEncryptLink = "";
                if (mobileEncryptLinkCell != null) {
                    mobileEncryptLink = mobileEncryptLinkCell.getStringCellValue();
                }
                data.setMobileEncryptLink(mobileEncryptLink);

                Cell plainLinkCell = row.getCell(13);
                String plainLink = "";
                if (plainLinkCell != null) {
                    plainLink = plainLinkCell.getStringCellValue();
                }
                data.setPlainLink(plainLink);

                Cell mobilePlainLinkCell = row.getCell(14);
                String mobilePlainLink = "";
                if (mobilePlainLinkCell != null) {
                    mobilePlainLink = mobilePlainLinkCell.getStringCellValue();
                }
                data.setMobilePlainLink(mobilePlainLink);

                String brandImgUrl = row.getCell(15).getStringCellValue();
                data.setBrandImgUrl(brandImgUrl);

                String categoryImgUrl = row.getCell(16).getStringCellValue();
                data.setCategoryImgUrl(categoryImgUrl);

                allModifiedData.add(data);
            }

            List<String> brandSql = new ArrayList<>();
            for (ModifiedExportData data : allModifiedData) {
                String sql = "INSERT INTO " + SCHEMA + ".tg_brand (";
                sql += "brand_id, brand_name, brand_logo_png, brand_logo_url, brand_channel, brand_type, ";
                sql += "t_money_return, classification_first_ids, url_type, brand_Mwebsite_url, brand_website_url, ";
                sql += "del_flg, add_user, add_time, upd_user, upd_time) VALUES (";

                sql += data.getId() + ", ";//brand_id
                sql += "'" + escape(data.getActivityName()) + "', ";//brand_name
                sql += "'" + escape(data.getCategoryImgUrl()) + "', ";//brand_logo_png -> category_img_url
                sql += "'" + escape(data.getBrandImgUrl()) + "', ";//brand_logo_url -> brand_img_url
                sql += "'2', ";//brand_channel
                sql += "'1', ";//brand_type
                sql += "'" + escape(data.getCommission()) + "', ";//t_money_return
                sql += "'" + escape(data.getActivityCategory()) + "', ";//classification_first_ids -> category_name
                sql += data.getUrlType() + ", ";//url_type
                sql += "'" + escape(data.getMobileEncryptLink()) + "', ";//brand_Mwebsite_url
                sql += "'" + escape(data.getEncryptLink()) + "', ";//brand_website_url
                sql += "'0', ";//del_flg
                sql += "'admin', ";//add_user
                sql += "CURRENT_TIMESTAMP, ";//add_time
                sql += "'admin', ";//upd_user
                sql += "CURRENT_TIMESTAMP); ";//upd_time

                brandSql.add(sql);
            }

            String updateSqlBrand1 = "UPDATE " + SCHEMA + ".tg_commodity SET brand_id = 339 WHERE brand_id = 1;";//京东
            // 1号店 2 -> 2
            String updateSqlBrand3 = "UPDATE " + SCHEMA + ".tg_commodity SET brand_id = 518 WHERE brand_id = 3;";//考拉海购

            File brandFile = new File("brand-all.sql");

            writer = new FileWriter(brandFile);
            writer.append("TRUNCATE " + SCHEMA + ".tg_brand;\n");
            for (String sql : brandSql) {
                writer.append(sql);
                writer.append("\n");
            }

            writer.append(updateSqlBrand1);
            writer.append("\n");
            writer.append(updateSqlBrand3);
            writer.append("\n");

            writer.flush();
            writer.close();
        } catch (IOException | InvalidFormatException ex) {
            ex.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
            if (io != null) {
                try {
                    io.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
            if (writer != null) {
                try {
                    writer.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    private String escape(String str) {
        if (str == null) return null;
        return str.replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "\\\\'").replaceAll("\"", "\\\\\"");
    }

    public static void main(String[] args) {
        ExcelBrandCategory app = new ExcelBrandCategory();
        app.run();
    }
}
